﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceIndexes]
#-- Purpose:		Provides a selective mechanism for index maintenance
#--	Last Update:	08/29/2011
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceIndexes]
(
	@database_name	sysname
)
AS

SET NOCOUNT ON

--- The name of the database is a mandatory parameter
IF ISNULL(@database_name, '') = ''
  BEGIN
	RAISERROR ('The name of the database is required (@database_name)', 16, 1)
	RETURN
  END

--- Do Not Process tempdb
IF @database_name = 'tempdb'
	RETURN

--- Declare Local Variables
DECLARE @sCommand varchar(8000), @readonly bit, @offline bit

--- Determine the state of the database
EXEC	@offline = dbo.spIsDatabaseOffline @database_name
EXEC	@readonly = dbo.spIsDatabaseReadOnly @database_name

--- Only attempt the backup on an ONLINE database
IF @offline = 0 AND @readonly = 0
  BEGIN
	SELECT	@sCommand = 'EXEC dbo.spMaintenanceIndexDefrag @database_name = [' + @database_name + '];'
	EXEC	(@sCommand)
	
	IF DATEPART(weekday, GETDATE()) = 7
	  BEGIN
		BEGIN TRY
			--- Add an if statement around update statistics to validate the table still exists?
			SELECT	@sCommand = 'USE [' + @database_name + ']; ',
					@sCommand = @sCommand + 'EXEC	sp_MSforeachtable ''PRINT ''''Updating statistics for ?''''; UPDATE STATISTICS ? WITH FULLSCAN;'''
			EXEC	(@sCommand)
		END TRY
		BEGIN CATCH
			--- Log the error - if possible
			DECLARE @sResults varchar(8000)
			SELECT @sResults = 'The database ' + @database_name + ' failed to update statistics (' + @sCommand + ').'
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sResults = @sResults + CHAR(13) 
								+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
								+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
								+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
								+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

			--- Print the error message to sysout
			PRINT @sResults

			--- Insert the error in the database
			INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
				VALUES (@database_name, GETDATE(), 'update_stats', 0, @sResults)
		END CATCH
	  END
  END
ELSE
	PRINT 'The database ' + @database_name + ' is not in ONLINE mode or is READ-ONLY'
	
SET NOCOUNT OFF
